<?php

/**
 * This class sends queries to database end returns the results. 
 * It takes inputs from user specified points' coordinats
 *
 * @author Ahmet
 */
class HatBulucu {
    
    //4 coordinats of given 2 points and result array to be return 
    public $fromX;
    public $fromY;
    public $toX;
    public $toY;
    public $result;
    
    //creates class object from given parameter array
    public function __construct($res) {
        include_once 'connect.php';
        $this->fromX = $res['fromX'];
        $this->fromY = $res['fromY'];
        $this->toX = $res['toX'];
        $this->toY = $res['toY'];
    }
    
    //Includes query that finds the single route for two specified points
    public function tekHatBul() {
        
        //It looks for any single route that has close stop points to each specified points
        $qu = "select distinct d1.kod, d1.sira as fSira,d2.sira as tSira, d1.px as fLat, d1.py as fLng, d2.px as tLat, d2.py as tLng, h.adi from duraklar d1, duraklar d2, hatlar h where 
		d1.px between ".$this->fromX."-0.003 and ".$this->fromX."+0.003 and 
		d1.py between ".$this->fromY."-0.006 and ".$this->fromY."+0.006 and 
		d2.px between ".$this->toX."-0.003 and ".$this->toX."+0.003 and 
		d2.py between ".$this->toY."-0.006 and ".$this->toY."+0.006 and
		d1.kod=d2.kod and d1.kod=h.kod and d1.sira<d2.sira group by d1.kod";
        
        /*
        $qu="Select a.* from
            (select distinct d1.kod, d1.sira as fSira,d1.distance as d1d, d2.sira as tSira,d2.distance as d2d, d1.px as fLat, d1.py as fLng, d2.px as tLat, d2.py as tLng, h.adi 
            from 
            (select *
               from (SELECT *,( 6371 * acos( 
                            cos( radians(" . $this->fromX . ") ) 
                            * cos( radians( px ) ) 
                            * cos( 
                                radians( py ) - radians(" . $this->fromY . ") ) 
                            + sin( radians(" . $this->fromX . ") ) 
                                * sin( radians( px ) ) ) ) as distance
                FROM duraklar
                ORDER BY distance) as b
                where b.distance < 0.3) as d1,
                (select *
                from (SELECT *,( 6371 * acos( 
                            cos( radians(" . $this->toX . ") ) 
                            * cos( radians( px ) ) 
                            * cos( 
                                radians( py ) - radians(" . $this->toY . ") ) 
                            + sin( radians(" . $this->toX . ") ) 
                                * sin( radians( px ) ) ) ) as distance
                FROM duraklar
                ORDER BY distance) as a
                where a.distance < 0.3) as d2, hatlar h 

            where 

                            d1.kod=d2.kod and d1.kod=h.kod and d1.sira<d2.sira order by fSira DESC , tSira ASC) as a
            group by kod";
         * 
         */
	
	$query = mysql_query($qu);
        
	$this->result = array();
        
	//populating the result array	
	while ($islem = mysql_fetch_array($query, MYSQL_ASSOC)) {
            array_push($this->result, $islem);
	}
        return $this->result;
    }
    
    //Includes query that finds the two possible routes for two specified points.
    public function ikiHatBul() {
        //create two temporary tables for holding the route info that close to each two points
        $q0 = "create table temp(kod varchar(10), sira INT, lat float(8,6), lng float(8,6))";
        
        $query = mysql_query($q0);
        
        $q1 = "create table temp2(kod varchar(10), sira INT, lat float(8,6), lng float(8,6))";
        
        $query = mysql_query($q1);

        //populates temporary tables with route info
        $q2 = "INSERT INTO temp(kod, sira,lat,lng) 
            (select distinct d1.kod, d1.sira, d1.px, d1.py
                from duraklar d1, (select distinct d2.kod,MIN(ABS(d2.px-" . $this->fromX . " + d2.py-" . $this->fromY . ")) as fark from duraklar d2 
                                    where 
                                    d2.px between " . $this->fromX . "-0.003 and " . $this->fromX . "+0.003 
                                    and 
                                    d2.py between " . $this->fromY . "-0.006 and " . $this->fromY . "+0.006 group by d2.kod) as a
                where 
                d1.px between " . $this->fromX . "-0.003 and " . $this->fromX . "+0.003 
                and 
                d1.py between " . $this->fromY . "-0.006 and " . $this->fromY . "+0.006
                and
                ABS(d1.px-" . $this->fromX . " + d1.py-" . $this->fromY . ")= a.fark
                and
                d1.kod = a.kod
                )";

        $query = mysql_query($q2);

         $q3 = "INSERT INTO temp2(kod, sira,lat,lng) 
            (select distinct c1.kod, c1.sira, c1.px, c1.py
                from duraklar c1,
                            (select distinct d2.kod, MIN(ABS(d2.px-" . $this->toX . " + d2.py-" . $this->toY . ")) as fark from duraklar d2 where 
                            d2.px between " . $this->toX . "-0.003 and " . $this->toX . "+0.003 and 
                            d2.py between " . $this->toY . "-0.006 and " . $this->toY . "+0.006 group by d2.kod) as b
                where
                c1.px between " . $this->toX . "-0.003 and " . $this->toX . "+0.003 and 
                c1.py between " . $this->toY . "-0.006 and " . $this->toY . "+0.006 and
                ABS(c1.px-" . $this->toX . " + c1.py-" . $this->toY . ")= b.fark and
                c1.kod = b.kod)";

        $query = mysql_query($q3);
        
        //Execute query on temporary tables. 
        //find out if there are stop points which are close to each other. 
        //If there is return the route infos and close stop infos 
        $q4 = "select distinct a1.kod as akod,a1.abin,a1.a1lat,a1.a1lng,a1.sira as ain,a1.px as a2lat, a1.py as a2lng, b1.kod as bkod,b1.sira as bbin,b1.px as b1lat, b1.py as b1lng,b1.bin,b1.b2lat,b1.b2lng,MIN(ABS(a1.px-b1.px + a1.py-b1.py)) as fark, (Select adi from hatlar where a1.kod = kod) as aadi, (Select adi from hatlar where b1.kod = kod) as badi    
                    from
                (select a.*, t.sira as abin, t.lat as a1lat, t.lng as a1lng from duraklar a, temp t where a.sira >= t.sira and a.kod = t.kod ) as a1,
                (select b.*, t2.sira as bin, t2.lat as b2lat, t2.lng as b2lng from duraklar b, temp2 t2 where b.sira <= t2.sira and b.kod = t2.kod ) as b1
                where 
                        a1.px between b1.px-0.0003 and b1.px+0.0003 and 
                        a1.py between b1.py-0.0006 and b1.py+0.0006 and 
                        a1.kod!=b1.kod 
                        group by akod,bkod HAVING MAX(bbin) ORDER BY fark LIMIT 10";
        
        $query = mysql_query($q4);
        
        $this->result = array();
	//populating the result array		
	while ($islem = mysql_fetch_array($query, MYSQL_ASSOC)) {
            array_push($this->result, $islem);
	}
        //drop the temporary tables
        $query = mysql_query("drop table temp;");
        $query = mysql_query("drop table temp2;");
        return $this->result;
    }
}

?>
